drop table if exists mpa, filmGenre, genres, likes, friends, users, films;
----DROP TABLE IF EXISTS users CASCADE;
----DROP TABLE IF EXISTS friends CASCADE;
----DROP TABLE IF EXISTS genres CASCADE;
----DROP TABLE IF EXISTS mpa CASCADE;
----DROP TABLE IF EXISTS films CASCADE;
----DROP TABLE IF EXISTS likes CASCADE;
----DROP TABLE IF EXISTS filmGenre CASCADE;
--
--create table if not exists films (
--    film_id integer generated by default as identity primary key,
--    film_name varchar(255) not null,
--    film_description varchar(255),
--    release_date date not null,
--    duration integer not null,
--    mpa_id integer
--);
create table if not exists MPA (
    mpa_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mpa_name varchar(255) not null unique
);

CREATE TABLE IF NOT EXISTS films(
    film_id     INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    film_name   VARCHAR(255) NOT NULL,
    film_description VARCHAR(200) NOT NULL,
    release_date DATE NOT NULL,
    duration    INT,
    mpa_id   INT
);
--
--create table if not exists films (
--    film_id integer generated by default as identity primary key,
--    film_name varchar(255),
--    film_description varchar(255),
--    release_date date not null,
--    duration integer not null
----    mpa_id INTEGER REFERENCES mpa (mpa_id) ON DELETE CASCADE
--);

create table if not exists users (
    user_id integer generated by default as identity primary key,
    user_name varchar(255),
    user_login varchar(255) not null,
    email varchar(255),
    birthday date
);

create table if not exists filmGenre (
    film_id integer,
    genre_id integer
);

create table if not exists genres (
    genre_id integer generated by default as identity primary key,
    genre_name varchar(255) not null unique
);

create table if not exists likes (
    film_id integer,
    user_id integer
);

create table if not exists friends (
    id integer generated by default as identity primary key,
    user_id integer,
    friend_id integer,
    friend_request_confirmed boolean not null default 0
);